create database ATJB;
go
use ATJB

drop database ATJB

--drop table Visistor;
create table Tour(
	id int identity(1,1) primary key,
	tourName varchar(50) not null,
	place varchar(100) not null,
	startDate datetime not null,
	endDate datetime not null,
	price money not null,
	images varchar(max),
	maxVisitors smallint not null,
	currentVisitors smallint not null,
	tag varchar(max),
    constraint check_maxVisitors check (maxVisitors >= currentVisitors)
);

create table Visitor(
	id int identity(1,1) primary key,
	userName varchar(50) unique not null,
	password varchar(50) not null,
	firstName varchar(20) not null,
	lastName varchar(20) not null,
	gender varchar(6),
	telephone char(20),
	email varchar(50) unique,
	area varchar(20) not null,
	hobby varchar(50),
	description varchar(max),
	[role] tinyint default 0 ,  /* Note: 0-Visitor, 1-admin */
);

create table BookTour(
	id int identity(1,1) primary key,
	visitorID int foreign key references Visitor(id),
	tourID int foreign key references Tour(id),
	numberOfSlot smallint,
	bookingDay datetime,
);

drop table Tour
drop table Visitor
drop table BookTour

select * from BookTour

--add BookTour procedure
drop proc addBookTour

create proc addBookTour(@visitorID int, @tourID int, @numberOfSlot smallint, @bookingDay datetime)	
as
begin 
	insert into BookTour values(@visitorID, @tourID, @numberOfSlot, @bookingDay)
end

-- CREATE TRIGGER updateTour_BookTour 
CREATE TRIGGER updateTour_BookTour 
ON BookTour 
FOR INSERT
AS
UPDATE Tour
SET Tour.currentVisitors = Tour.currentVisitors + inserted.numberOfSlot 
FROM Tour INNER JOIN inserted
ON Tour.id = inserted.tourID


-- get list booktour 
drop proc getListBookTour

create proc getListBookTour
as 
begin
    select bt.id, v.userName, t.tourName, t.place , t.startDate , t.endDate ,bt.numberOfSlot, bt.bookingDay 
    from Visitor v inner join BookTour bt on v.id = bt.tourID
    inner join Tour t on t.id = bt.tourID
end

exec getListBookTour

--add Tour procedure
drop proc addTour

create proc addTour(@tourName varchar(50), @place varchar(100), @startDate datetime, @endDate datetime, @price money,
					@images varchar(max), @maxVisitors smallint, @currentVisitors smallint, @tag varchar(max))
as
begin
	insert into Tour values(@tourName, @place, @startDate, @endDate, @price, @images, @maxVisitors, @currentVisitors,@tag);
end



--add visitor procedure 
drop proc addVisitor

create proc addVisitor(@userName varchar(20), @password varchar(50), @firstName varchar(20), @lastName varchar(20),
						@gender varchar(6), @telephone char(20), @email varchar(30), @area varchar(20), @hobby varchar(50), @description varchar(max))
as
begin
	insert into Visitor(userName, password, firstName, lastName, gender, telephone, email, area, hobby, description)
	values(@userName, @password, @firstName, @lastName, @gender, @telephone, @email, @area, @hobby, @description);
end

select * from Tour

execute addVisitor 'thuynd2', '123456', 'Thuy', 'Nguyen Dac', 'Male', '1234567', 'email@gmail.com', 'area', 'hobby', 'description'
execute addVisitor 'thanhpv7', '123456', 'Thanh', 'Pham Van', 'Male', '1234567', 'email1@gmail.com', 'area', 'hobby', 'description'
execute addVisitor 'thuhq', '123456', 'Thu', 'Hoa Quoc', 'Male', '1234567', 'email4@gmail.com', 'area', 'hobby', 'description'
execute addVisitor 'quyetnv1', '123456', 'Quyet', 'Nguyen Van', 'Male', '1234567', 'email123@gmail.com', 'area', 'hobby', 'description'

update Visitor 
set [role] = 1
where userName = 'thanhpv7'

select * from Visitor

delete from visitor

--update current visitor in Tour table when a visitor book this tour
create proc updateCurrentVisitors 
	(@idTour int,
	@idBook int)
as
begin
	update Tour
	set currentVisitors = (select currentVisitors from Tour where id = @idTour) + (select numberOfSlot from BookTour where id = @idBook)
	where id = @idTour;
end
--drop proc updateCurrentVisitors
--exec addTour 1,'sapa', 'sapa', '10/12/2014', '14/12/2014', 3000000, 'asdfakfads.jpg', 30, 11
--select * from Tour
--exec updateCurrentVisitors 1


--select info from Visitor to login
create proc checkVisitor 
	@userName varchar(20),
	@password varchar(50)
as
begin
	select id, userName, [password], firstName, lastName, gender, telephone, email, area, hobby, [description], [role]
	from Visitor
	where userName = @userName and password = @password
end

drop proc checkVisitor

exec checkVisitor 'thanhpv7', 'e10adc3949ba59abbe56e057f20f883e'

-- select info from Visitor to check remember login
create proc checkReVisitor
	@userName varchar(20) 
as
begin
	select id, userName, password, firstName, lastName, gender, telephone, email, area, hobby, description, [role]
	from Visitor
	where userName = @userName
end

drop proc checkReVisitor

--thu tuc search
create proc SearchName @firstName varchar(20)
as
declare @s varchar(30)
set @s = '%'+@firstName+'%';
begin
	select firstName, lastName, gender, telephone, area, hobby, description
	from Visitor
	where firstName like @s
	order by (select charindex(@firstName, firstName))
end

drop proc SearchName

--tour detail
create proc getTourDetail @id int
as
begin
select * from Tour
where id = @id
end

execute getTourDetail 3

drop proc SearchTour

--tour SearchTour
create proc SearchTour @name varchar(20)
as
declare @s varchar(20)
set @s = '%' + @name + '%';
begin
    select * from tour
    where tourName like @s
end 

execute SearchTour 'bn'

-- reset password for visitor
drop proc resetPassword
create proc resetPassword @email varchar(50), @password varchar(50)
as
begin 
    update visitor
    set password = @password
    where email = @email
end
